package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.common.OrderCode;
import cn.com.dashihui.web.dao.GbOrder;
import cn.com.dashihui.web.dao.GoodsStore;
import cn.com.dashihui.web.dao.GoodsTokerValue;
import cn.com.dashihui.web.dao.Logistics;
import cn.com.dashihui.web.dao.Order;
import cn.com.dashihui.web.dao.OrderLogistics;
import cn.com.dashihui.web.dao.Refound;
import cn.com.dashihui.web.dao.SendSeller;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.User;

public class OrderService {

	/**
	 * 分页查找订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPage(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_list tbol "
				+ "LEFT JOIN t_bus_order A ON tbol.orderNum = A.orderNum "
				+ "inner JOIN t_bus_user tbu on A.userid=tbu.id "
				+ "LEFT JOIN t_bus_goods tbg ON tbol.goodsid = tbg.id "
				+ "LEFT JOIN t_bus_goods_store_rel tbgsr ON tbgsr.goodstore = tbg.good_store "
				+ "LEFT JOIN t_dict_store tds ON tds.storeid = tbgsr.storeid "
				+ "LEFT JOIN t_bus_seller tbs on tbs.sellerId=tds.sellerId "
				+ "LEFT JOIN t_bus_order_toker_user_log tbotul ON A.orderNum=tbotul.orderNum "
				+ "LEFT JOIN t_bus_user B ON tbu.toBossOneId = B.id "
				+ "where 1=1 "
				);

		System.out.println("订单筛选"+sellerId+storeid);
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND tbs.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeid)&&!"0".equals(storeid)){
			sBuffer.append(" AND tds.storeid in ('"+storeid+"')");
		}

		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 7:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 9:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
	
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
			sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,tbs.sellerName,tds.title as xdstore,tbu.id as uid,tbu.name,tbu.card,tbu.toBossOneId,tbotul.totalPoint,tbotul.totalDetion,tbotul.totalDcount,tbotul.id as tokerid ,B.name as jjrName",sBuffer.toString(),params.toArray());
		return page;
	}
	/**
	 * 分页查找订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPageTui(int pageNum,int pageSize,String orderNum,int state,String tel,String name,int tuiState){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_list tbol "
				+ "LEFT JOIN t_bus_order A ON tbol.orderNum = A.orderNum "
				+ "LEFT JOIN t_bus_order_refund C ON tbol.orderNum = C.orderNum "
				+ "inner JOIN t_bus_user tbu on A.userid=tbu.id "
				+ "LEFT JOIN t_bus_goods tbg ON tbol.goodsid = tbg.id "
				+ "LEFT JOIN t_bus_goods_store_rel tbgsr ON tbgsr.goodstore = tbg.good_store "
				+ "LEFT JOIN t_dict_store tds ON tds.storeid = tbgsr.storeid "
				+ "LEFT JOIN t_bus_seller tbs on tbs.sellerId=tds.sellerId "
				+ "LEFT JOIN t_bus_order_toker_user_log tbotul ON A.orderNum=tbotul.orderNum "
				+ "LEFT JOIN t_bus_user B ON tbu.toBossOneId = B.id "
				+ "where 1=1 "
				);

		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		switch (tuiState) {
		case 1:
			sBuffer.append(" AND A.tuistate=?");
			params.add(tuiState);
			break;
		case 2:
			sBuffer.append(" AND A.tuistate=?");
			params.add(tuiState);
			break;
		case 3:
			sBuffer.append(" AND A.tuistate=?");
			params.add(tuiState);
			break;
		case 4:
			sBuffer.append(" AND A.tuistate=?");
			params.add(tuiState);
			break;
		case 5:
			sBuffer.append(" AND A.tuistate=?");
			params.add(tuiState);
			break;
		}
	
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND tbu.phone like ? ");
			params.add("%"+tel+"%");
		}
		if(!StrKit.isBlank(name)){
			sBuffer.append(" AND tbu.name like ? ");
			params.add("%"+name+"%");
		}
			sBuffer.append(" AND A.tuistate!=0");
			sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,C.refundReason,tbs.sellerName,tds.title as xdstore,tbu.id as uid,tbu.phone as phone,tbu.name,tbu.card,tbu.toBossOneId,tbotul.totalPoint,tbotul.totalDetion,tbotul.totalDcount,tbotul.id as tokerid ,B.name as jjrName",sBuffer.toString(),params.toArray());
		return page;
	}
	/**
	 * 分页查找团购商品订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPagegb(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid,int isFree,int selectGoods){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_gb A "
				+ "inner JOIN t_bus_user tbu on A.userid=tbu.id "
				+ "LEFT JOIN t_bus_goods tbg ON A.goodsid = tbg.id "
				+ "LEFT JOIN t_bus_goods_store_rel tbgsr ON tbgsr.goodstore = tbg.good_store "
				+ "LEFT JOIN t_dict_store tds ON tds.storeid = tbgsr.storeid "
				+ "LEFT JOIN t_bus_seller tbs on tbs.sellerId=tds.sellerId "
				+ "LEFT JOIN t_bus_order_toker_user_log tbotul ON A.orderNum=tbotul.orderNum "
				+ "LEFT JOIN t_bus_user B ON tbu.toBossOneId = B.id "
				+ "where 1=1 ");
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND tbs.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeid)&&!"0".equals(storeid)){
			sBuffer.append(" AND tds.storeid in ('"+storeid+"')");
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(selectGoods!=0){
			sBuffer.append(" AND A.goodsid=? ");
			params.add(selectGoods);
		}
		if(isFree!=9){
			sBuffer.append(" AND A.isFree=? ");
			params.add(isFree);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 7:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 9:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}

			sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> pagegb = Db.paginate(pageNum,pageSize,"SELECT A.*,tbs.sellerName,tds.title as xdstore,tbu.id as uid,tbu.name,tbu.card,tbu.toBossOneId,tbotul.totalPoint,tbotul.totalDetion,tbotul.totalDcount,tbotul.id as tokerid,B.name as jjrName",sBuffer.toString(),params.toArray());
		return pagegb;
	}
	/**
	 * 查询所需积分”总和，“现金价格”总和,退款价格总和，实际收入
	 */
	public Record findTotal(int pageNum, int pageSize, String orderNum, String beginDate, String endDate, int state,
			String sellerId, String tel, String storeId){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT IFNULL(ROUND(SUM(amount), 2),0.00) amount,"
				+ "IFNULL(SUM(payPoint),0) payPoint,"
				+ "IFNULL(ROUND(SUM(amount1), 2),0.00) tuikuan1,"
				+ "IFNULL(ROUND(SUM(amount2), 2),0.00) duihuan,"
				+ "IFNULL(ROUND(SUM(amount3), 2),0.00) nduihuan ,"
				+ "ROUND(IFNULL(SUM(amount3),0.00), 2) as shiji  "
				+ "from ( select case when  A.payState=2 AND (A.tuistate=2 or A.tuistate=4) then A.amount else 0 end as amount1,"
				+ "case when A.payState=2 AND A.orderState=2 then A.amount else 0.00 end as amount2,"
				+ "case when A.payState=2 AND A.tuistate!=2 AND A.tuistate!=4 then A.amount else 0.00 end as amount3,"
				+ "case when A.orderState!=5 AND A.orderState!=1 AND A.orderState!=7 AND A.orderState!=9 AND A.tuistate!=2 AND A.tuistate!=4 then A.payPoint else 0.00 end as payPoint,A.amount "
				+ "FROM t_bus_order A "
				+ "left join t_bus_user B on B.id = A.userid "
				+ "inner join t_bus_goods tg on A.goodsid = tg.id "
				+ "inner join t_bus_goods_store_rel C on C.goodstore = tg.good_store "
				+ "inner join t_dict_store D on D.storeid = C.storeid "
				+ "inner join t_bus_seller E on E.sellerId = D.sellerId "
				+ "WHERE 1=1 ");
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND E.sellerId in ('"+sellerId+"')");
		}
		System.out.println(storeId);
		if(!StrKit.isBlank(storeId)){
			sBuffer.append(" AND D.storeid in ('"+storeId+"')");
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel like ?");
			params.add("%"+tel+"%");
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 7:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 9:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		sBuffer.append(" ) a ");
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;	
	}
	/**
	 * 查询团购订单所需积分”总和，“现金价格”总和,退款价格总和，实际收入
	 */
	public Record findTotalgb(int pageNum, int pageSize, String orderNum, String beginDate, String endDate, int state,
			String sellerId, String tel, String storeId,int isFree){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT IFNULL(ROUND(SUM(amount), 2),0.00) amount,"
				+ "IFNULL(ROUND(SUM(amount1), 2),0.00) tuikuan1,"
				+ "ROUND(IFNULL(SUM(amount3),0.00), 2) as shiji  "
				+ "from ( select case when  A.payState=2 AND (A.tuistate=2 or A.tuistate=4) then A.amount else 0 end as amount1,"
				+ "case when A.payState=2 AND A.orderState=2 then A.amount else 0.00 end as amount2,"
				+ "case when A.payState=2 AND A.tuistate!=2 AND A.tuistate!=4 then A.amount else 0.00 end as amount3,"
				+ "case when A.orderState!=5 AND A.orderState!=1 AND A.orderState!=7 AND A.orderState!=9 AND A.tuistate!=2 AND A.tuistate!=4 then A.id else 0.00 end as iddddd,A.amount "
				+ "FROM t_bus_order_gb A "
				+ "inner join t_bus_user B on B.id = A.userid "
				+ "left join t_bus_goods tg on A.goodsid = tg.id "
				+ "inner join t_bus_goods_store_rel C on C.goodstore = tg.good_store "
				+ "inner join t_dict_store D on D.storeid = C.storeid "
				+ "inner join t_bus_seller E on E.sellerId = D.sellerId "
				+ "WHERE 1=1 ");
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND E.sellerId in ('"+sellerId+"')");
		}
		System.out.println(storeId);
		if(!StrKit.isBlank(storeId)){
			sBuffer.append(" AND D.storeid in ('"+storeId+"')");
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(isFree!=9){
			sBuffer.append(" AND A.isFree=? ");
			params.add(isFree);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 7:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 9:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		sBuffer.append(" ) a ");
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;	
	}
	
	//查询订单
	public Page<Record> goodsOrder(int pageNum, int pageSize, String orderNum, String beginDate, String endDate, int state,int seid, String tel, int stid, int id) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order A "
				+ "inner JOIN t_bus_user F on A.userid=F.id "
				+ "LEFT JOIN t_dict_store B ON F.toBossTwoId = B.id "
				+ "LEFT JOIN t_bus_seller C on F.toBossThreeId=C.id "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id "
				+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
				+ "LEFT JOIN t_dict_store E ON A.dhstoreid = E.storeid where 1=1  and A.goodsid=?");
		params.add(id);
		if(seid != 0){
			sBuffer.append(" AND F.toBossThreeId=? ");
			params.add(seid);
		}
		if(stid != 0){
			sBuffer.append(" AND F.toBossTwoId=? ");
			params.add(stid);
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 7:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 9:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		 /*
			default:
				//默认支付方式
				sBuffer.append(" AND A.state!=1");
				break;*/
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		
		sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,B.title as xdstore,F.id as uid,F.name,F.card,F.toBossOneId,E.title as dhstore,C.legalPerson as jjrName,L.totalPoint,L.totalDetion,L.totalDcount,L.id as tokerid",sBuffer.toString(),params.toArray());
		return page;
	}
	//查询团购订单
	public Page<Record> ordergb(int pageNum, int pageSize, int userid) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_gb A "
				+ "left join t_bus_goods B on B.id = A.goodsid "
				+ " where 1=1 and A.orderState!=1 and A.orderState!=5 and A.userid=?");
		params.add(userid);
		
		sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,B.name as goodsName",sBuffer.toString(),params.toArray());
		return page;
	}
	/**
	 * 查询当前用户的团购订单
	 * @return
	 */
		public Page<Record> goodsOrdergb(int pageNum, int pageSize, String orderNum, String beginDate, String endDate, int state,String sellerId, String tel, String storeId, int id) {
			StringBuffer sBuffer = new StringBuffer();
			List<Object> params = new ArrayList<Object>();
			sBuffer.append(" FROM t_bus_order_gb A LEFT JOIN t_dict_store B ON A.storeid = B.storeid "
					+ "LEFT JOIN t_bus_seller C on B.sellerId=C.sellerId "
					+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id "
					+ "LEFT JOIN t_bus_user F on A.userid=F.id "
					+ "left JOIN t_dict_store E ON A.dhstoreid = E.storeid where 1=1 and A.goodsid=?");
			params.add(id);
			if(!StrKit.isBlank(sellerId)){
				sBuffer.append(" AND C.sellerId=?");
				params.add(sellerId);
			}
			if(!StrKit.isBlank(storeId)){
				sBuffer.append(" AND A.storeid=?");
				params.add(storeId);
			}
			if(!StrKit.isBlank(orderNum)){
				sBuffer.append(" AND A.orderNum=?");
				params.add(orderNum);
			}
			if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
				sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
				params.add(beginDate);
				params.add(endDate);
			}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
				sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
				params.add(beginDate);
			}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
				sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
				params.add(endDate);
			}
			switch (state) {
			case 1:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 2:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 3:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 4:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 5:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 6:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 7:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			case 9:
				sBuffer.append(" AND A.orderState=?");
				params.add(state);
				break;
			}
			if(!StrKit.isBlank(tel)){
				sBuffer.append(" AND A.tel=?");
				params.add(tel);
			}
			
			sBuffer.append(" ORDER BY A.startDate DESC");
			//查询出符合条件的订单列表
			Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,B.title as xdstore,F.name,F.card,F.toBossOneId,E.title as dhstore",sBuffer.toString(),params.toArray());
			return page;
		}
	/**
	 * 根据订单号查询出订单配送地址
	 */
	public Order findOrderByOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order A "
				+ " WHERE A.orderNum=?";
		return Order.me().findFirst(sql,orderNum);
	}
	/**
	 * 根据团购订单号查询出订单配送地址
	 */
	public Record findgbOrderByOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order_gb A "
				+ " left join t_bus_user_address B on B.id = A.addressid "
				+ " WHERE A.orderNum=?";
		return Db.findFirst(sql,orderNum);
	}
	/**
	 * 根据团购订单号查询出商品清单
	 */
	public List<Record> findGoodsListBygbOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order_gb A "
				+ " left join t_bus_goods C on C.id = A.goodsid "
				+ " WHERE orderNum=?";
		return Db.find(sql, orderNum);
	}
	/**
	 * 根据订单号查询出商品清单
	 */
	public List<Record> findGoodsListByOrderNum(String orderNum){
		String sql = "SELECT A.orderNum,A.goodsid,A.name as title,A.price,A.count,A.amount,B.name FROM t_bus_order_list A left join t_bus_goods B on A.goodsid = B.id WHERE A.orderNum=?";
		return Db.find(sql, orderNum);
	}

	/**
	 * 根据团购订单号查询订单操作日志
	 */
	public List<Record> findLogListBygbOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order_gb_log A"
				+ " left join t_bus_user B on B.id = A.user"
				+ " WHERE A.orderNum=? ORDER BY A.createDate";
		return Db.find(sql, orderNum);
	}
	/**
	 * 根据订单号查询订单操作日志
	 */
	public List<Record> findLogListByOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order_log WHERE orderNum=? ORDER BY createDate";
		return Db.find(sql, orderNum);
	}

	/**
	 * 根据订单号查询订单评价信息
	 */
	public Record findEvalByOrderNum(String orderNum){
		String sql = "SELECT * FROM t_bus_order_eval WHERE orderNum=?";
		return Db.findFirst(sql, orderNum);
	}
	/********************************************会员
	 * 查询用户信息
	 * @param id
	 * @return
	 */
	public User findById(int id){
		System.out.println(id);
		return User.me().findFirst("SELECT A.* ,ifnull(C.countcum,0) countnum ,ifnull(C.allCount,0) allCount, "
				+ " (year(now())-year(A.birthday)-1) + ( DATE_FORMAT(A.birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) as age "
				+ " FROM t_bus_user A left join t_bus_user_wx B on A.id=B.userid left join (select userid,count(id) as countcum,sum(amount) as allCount from "
				+ " t_bus_order where orderState=2 group by userid) C on C.userid= B.id WHERE A.id=?",id);
	}
	/** ************************************************会员
	 * 查询分公司信息
	 * @return
	 */
	public List<Store> findStoreList() {
		return Store.me().find("select storeid,title from t_dict_store");
	}
	/** **************************************************会员
	 * 查询真实名字
	 * @param store
	 * @return
	 */
	public List<Record> findUserList(String store) {
		return Db.find("select trueName from t_auth_admin_seller A left join t_dict_store B on A.storeid=B.storeid where B.title=?",store);
	}
	
	 
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "订单列表", titles, listz);
	}

	/** ****************************************
	 *获取所有的快递公司
	 * @return
	 */
	public List<Logistics> findAllLogistics() {
		return Logistics.me().find("SELECT * FROM t_dict_logistics");
	}
	/**
	 *查询所有的sendSeller
	 */
	public List<SendSeller> findSendSeller(){
		return SendSeller.me().find("SELECT * FROM t_dict_sendseller");
	}
	/**
	 * 查询当前订单
	 * @param id
	 * @return
	 */
	public Order findByOrderNum(String orderNum) {
		// TODO Auto-generated method stub
		return Order.me().findFirst("SELECT * FROM t_bus_order WHERE orderNum=?",orderNum);
	}
	/**
	 * 查询当前需要保存的团购订单物流信息的订单
	 * @param id
	 * @return
	 */
	public GbOrder findByOrderGbNum(String orderNum) {
		// TODO Auto-generated method stub
		return GbOrder.me().findFirst("SELECT * FROM t_bus_order_gb WHERE orderNum=?",orderNum);
	}
	/**
	 * 查询当前运单是否存在，一个运单不可以存在多张订单
	 * @param id
	 * @return
	 */
	public boolean findExistsByLogisticCode(String logisticCode) {
		// TODO Auto-generated method stub
		return OrderLogistics.me().findFirst("SELECT * FROM t_bus_order_logistics WHERE logisticCode=?",logisticCode)!=null;
	}
	/**
	 * 查询当前订单是否存在，一个顶单不可以多次发货
	 * @param id
	 * @return
	 */
	public boolean findExistsByOrderNum(String orderNum) {
		// TODO Auto-generated method stub
		return OrderLogistics.me().findFirst("SELECT * FROM t_bus_order_logistics WHERE orderCode=?",orderNum)!=null;
	}

	public List<Record> findExcelByPage(String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT A.*,C.sellerName,B.title as xdstore,F.id as uid,F.name,F.card,F.toBossOneId,E.title as dhstore,L.totalPoint,L.totalDetion,L.totalDcount,L.id as tokerid FROM t_bus_order A "
				+ "inner JOIN t_bus_user F on A.userid=F.id "
				+ "LEFT JOIN t_dict_store B ON F.toBossTwoId = B.id "
				+ "LEFT JOIN t_bus_seller C on F.toBossThreeId=C.id "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id "
				+ "LEFT JOIN t_bus_order_toker_user_log L ON A.orderNum=L.orderNum "
				+ "LEFT JOIN t_dict_store E ON A.dhstoreid = E.storeid where 1=1 ");
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeid)&&!"0".equals(storeid)){
			sBuffer.append(" AND A.storeid in ('"+storeid+"')");
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 7:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 9:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		
		sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		return Db.find(sBuffer.toString(),params.toArray());
	}
	/**
	 * 导出团购数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void exportgb(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "团购订单列表", titles, listz);
	}
	public List<Record> findExcelByPagegb(String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT A.*,B.title as xdstore,F.name,F.card,F.toBossOneId,E.title as dhstore "
				+ "FROM t_bus_order_gb A LEFT JOIN t_dict_store B ON A.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C on B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id "
				+ "LEFT JOIN t_bus_user F on A.userid=F.id  "
				+ "left JOIN t_dict_store E ON A.dhstoreid = E.storeid where 1=1 ");
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeid)&&!"0".equals(storeid)){
			sBuffer.append(" AND A.storeid in ('"+storeid+"')");
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
			case 1://待付款（
				sBuffer.append(" AND payState=? ");
				params.add(OrderCode.OrderPayState.NO_PAY);
				break;
			case 2://未兑换
				sBuffer.append(" AND payState=? and orderState=?  and tuistate=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.NO_CON);
				params.add(OrderCode.RefundState.ZERO);
				break;
			case 3://已付款
				sBuffer.append(" AND payState=? AND orderState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.had_CON);
				break;
			case 4://过期
				sBuffer.append(" AND payState=? AND orderState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.EXPIRE);
				break;
			case 5://删除
				sBuffer.append(" AND payState=? AND delState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.DelState.YES);
				break;
			case 6://取消
				sBuffer.append(" AND payState=? AND delState=?");
				params.add(OrderCode.OrderPayState.NO_PAY);
				params.add(OrderCode.DelState.YES);
				break;
			case 7://退款中
				sBuffer.append(" AND payState=? AND orderState=? and tuistate=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.NO_CON);
				params.add(OrderCode.RefundState.VERIFYING);
				break;
			case 8://已退款
				sBuffer.append(" AND payState=? AND orderState=? and tuistate=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.NO_CON);
				params.add(OrderCode.RefundState.REFUNDED);
				break;
	 
			/*default:
				//默认不查询、过期、的订单
				sBuffer.append(" AND orderState!=? ");
				params.add(OrderCode.OrderState.EXPIRE);
				//默认不查询取消、过期、退款、删除的订单
				sBuffer.append(" AND orderState!=? AND tuistate!=? AND tuistate!=?");
				params.add(OrderCode.OrderState.EXPIRE);
				params.add(OrderCode.RefundState.VERIFYING);
				params.add(OrderCode.RefundState.REFUNDED);
				break;*/
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		
		sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		return Db.find(sBuffer.toString(),params.toArray());
	}

	public List<Record> findGoodsExcelByPage(String orderNum, String beginDate, String endDate, int state,String sellerId, String tel, String storeId, int id) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT A.*,B.title as xdstore,F.name,F.card,F.toBossOneId,E.title as dhstore FROM t_bus_order A LEFT JOIN t_dict_store B ON A.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C on B.sellerId=C.sellerId "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id "
				+ "LEFT JOIN t_bus_user F on A.userid=F.id   "
				+ "left JOIN t_dict_store E ON A.dhstoreid = E.storeid where 1=1 and A.goodsid=?");
		params.add(id);
		if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND C.sellerId=?");
			params.add(sellerId);
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
			case 1://待付款（
				sBuffer.append(" AND payState=? ");
				params.add(OrderCode.OrderPayState.NO_PAY);
				break;
			case 2://未兑换
				sBuffer.append(" AND payState=? and orderState=?  and tuistate=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.NO_CON);
				params.add(OrderCode.RefundState.ZERO);
				break;
			case 3://已付款
				sBuffer.append(" AND payState=? AND orderState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.had_CON);
				break;
			case 4://过期
				sBuffer.append(" AND payState=? AND orderState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.EXPIRE);
				break;
			case 5://删除
				sBuffer.append(" AND payState=? AND delState=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.DelState.YES);
				break;
			case 6://取消
				sBuffer.append(" AND payState=? AND delState=?");
				params.add(OrderCode.OrderPayState.NO_PAY);
				params.add(OrderCode.DelState.YES);
				break;
			case 7://退款中
				sBuffer.append(" AND payState=? AND orderState=? and tuistate=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.NO_CON);
				params.add(OrderCode.RefundState.VERIFYING);
				break;
			case 8://已退款
				sBuffer.append(" AND payState=? AND orderState=? and tuistate=?");
				params.add(OrderCode.OrderPayState.HAD_PAY);
				params.add(OrderCode.OrderState.NO_CON);
				params.add(OrderCode.RefundState.REFUNDED);
				break;
			default:
				//默认不查询取消、过期、退款、删除的订单
				sBuffer.append(" AND orderState!=? AND tuistate!=? AND tuistate!=?");
				params.add(OrderCode.OrderState.EXPIRE);
				params.add(OrderCode.RefundState.VERIFYING);
				params.add(OrderCode.RefundState.REFUNDED);
				break;
	 
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		
		sBuffer.append(" ORDER BY A.startDate DESC");
		return  Db.find(sBuffer.toString(),params.toArray());
		
	}
	public Page<Record> findBydixianPage(int pageNum, int pageSize, String toBossOneId, String beginDate, String endDate,
			String sellerId, String phone, String storeId) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("FROM t_dict_store_dixian A LEFT JOIN t_dict_store B ON A.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C on B.sellerId=C.sellerId "
				+ "left join t_bus_user T on A.phone=T.phone "
				+ "left JOIN t_dict_store E ON A.storeid = E.storeid  where 1=1 ");
		
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeId)&&!"0".equals(storeId)){
			sBuffer.append(" AND A.storeid in ('"+storeId+"')");
		}
		if(!StrKit.isBlank(toBossOneId)){
			sBuffer.append(" AND T.toBossOneId=?");
			params.add(toBossOneId);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(!StrKit.isBlank(phone)){
			sBuffer.append(" AND A.phone=?");
			params.add(phone);
		}
		
		sBuffer.append(" ORDER BY A.date DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,T.name,T.store,T.toBossOneId,E.title,C.sellerName ",sBuffer.toString(),params.toArray());
		return page;
	}
	public List<Record> findExcelByPage(String toBossOneId, String beginDate, String endDate, String sellerId, String phone,String storeId) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT A.*,T.name,B.title as store,T.toBossOneId,E.title,C.sellerName FROM t_dict_store_dixian A LEFT JOIN t_dict_store B ON A.storeid = B.storeid "
				+ "LEFT JOIN t_bus_seller C on B.sellerId=C.sellerId "
				+ "left join t_bus_user T on A.phone=T.phone "
				+ "left JOIN t_dict_store E ON A.storeid = E.storeid  where 1=1 ");
		
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeId)&&!"0".equals(storeId)){
			sBuffer.append(" AND A.storeid in ('"+storeId+"')");
		}
		if(!StrKit.isBlank(toBossOneId)){
			sBuffer.append(" AND T.toBossOneId=?");
			params.add(toBossOneId);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.date BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.date,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(!StrKit.isBlank(phone)){
			sBuffer.append(" AND A.phone=?");
			params.add(phone);
		}
		sBuffer.append(" ORDER BY A.date DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}
	public void exportdi(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "积分抵现列表", titles, listz);
	}

	/**
	 * 分页查找订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findUserOrder(int pageNum,int pageSize,int userId){
			String sql=" FROM t_bus_order A "
					+ "left join t_bus_user_wx B on B.id=A.userid "
					+ "left join t_bus_user C on C.id=A.userid "
					+ "left join t_dict_store D on D.id=C.toBossTwoId "
					+ "left join t_bus_seller F on F.id=C.toBossThreeId "
					+ " where A.payState=2 AND C.id="+userId +" ORDER BY A.startDate DESC ";
			return Db.paginate(pageNum, pageSize,"SELECT A.*,C.*,D.title as storeName,F.sellerName ",sql);
	}
	/**
	 * 根据用户id导出订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public List<Record> findUserOrder(int userId){	
			return Db.find("SELECT A.*,C.*,D.title xdstore,E.title dhstore "
					+  "FROM t_bus_order A left join t_bus_user_wx B on B.id=A.userid "
					+ " left join t_bus_user C on C.id=B.userid left join t_dict_store D on D.storeid=A.storeid "
					+ " left join t_dict_store E on E.storeid=A.dhstoreid  where A.orderState=2 AND C.id="+userId +" ORDER BY A.startDate DESC");			
	}
	/**
	 * 根据用户id查询团购订单信息
	 */
	/*public List<Record> findUserOrdergb(int userId){	
			return Db.find("SELECT A.*,C.name FROM t_bus_order_gb A left join t_bus_user C on C.id=A.userid  where A.orderState=2 AND C.id="+userId +" ORDER BY A.startDate DESC");			
	}*/
	public GoodsTokerValue findToker(int tokerid) {
		// TODO Auto-generated method stub
		return GoodsTokerValue.me().findFirst("SELECT * FROM t_bus_order_toker_user_log WHERE id=?",tokerid);
	}
	public User findUser(int id) {
		// TODO Auto-generated method stub
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE id=?",id);
	}
	
	/**
	 * 查询团购商品各状态的订单数量
	 * @return
	 */
	public List<Record> gbStatsData() {
		return Db.find("select orderState as state,count(1) as count from t_bus_order_gb group by orderState order by orderState");
		
	}
	/**
	 * 查询所有团购商品各状态数量
	 * @return
	 */
	public List<Record> queryGbGoods() {
		return Db.find("select g.id,g.name,g.countnum,"
				+ "(select count(1) from t_bus_order_gb tg where tg.goodsid=g.id)as count1 ,"
				+ "(select count(1) from t_bus_order_gb tg where tg.goodsid=g.id and tg.orderState in(2,3,4,6))as count2 ,"
				+ "(select count(1) from t_bus_order_gb tg where tg.goodsid=g.id  and tg.isFree=3)as count3  "
				+ "from t_bus_goods g  where g.groupbuying=2");
	}
	public List<GoodsStore> findGoods() {
		// TODO Auto-generated method stub
		return GoodsStore.me().find("select id,name from t_bus_goods where groupbuying = 2");

	}
	/**
	 * 查询当前保存的订单物流信息的订单
	 * @param id
	 * @return
	 */
	public OrderLogistics findWBByOrderNum(String orderNum) {
		// TODO Auto-generated method stub
		return OrderLogistics.me().findFirst("SELECT * FROM t_bus_order_logistics WHERE orderCode=?",orderNum);
	}
	/**
	 * 查询退款单
	 * @param id
	 * @return
	 */
	public Refound findRefound(String orderNum) {
		// TODO Auto-generated method stub
		return Refound.me().findFirst("SELECT * FROM t_bus_order_refund WHERE orderNum=?",orderNum);
	}
	
}
